import datetime
import json

import pymysql

"""查询视频播放总时长"""


# 1上市生产 2上市测试 3非上市生产 4非上市测试
def get_connection(type):
    host = 'rm-2zej74580n3kmf93w9o.mysql.rds.aliyuncs.com'
    user = 'cloudpc_prod'
    password = 'k_z8_tKhS7WF_RCm'
    database = 'cloudpc_prod'
    charset = 'utf8'
    if type == '2':
        host = 'rm-2ze86ia90s73c90pw0o.mysql.rds.aliyuncs.com'
        user = 'cloudpc_dev'
        password = 'w0RZMCO_La3o'
        database = 'cloudpc_dev'
        charset = 'utf8'
    elif type == '3':
        host = 'rm-2ze98l8dhl893u752ao.mysql.rds.aliyuncs.com'
        user = 'cloudpc_prod'
        password = '36nEY($Z(tTn9gK'
        database = 'cloudpc_prod'
        charset = 'utf8'
    elif type == '4':
        host = 'rm-2ze98l8dhl893u752ao.mysql.rds.aliyuncs.com'
        user = 'cloudpc_dev'
        password = 'O4p&Iw&%B1nXYv'
        database = 'cloudpc_dev'
        charset = 'utf8'
    print('当前数据库是%s%s' % (get_type_name(type), user))
    con = pymysql.connect(host=host, user=user, password=password, db=database,
                          charset=charset)
    return con


def select_by_sql(con, sql):
    cursor = con.cursor()
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    rows = []
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = str(value)
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 1上市生产 2上市测试 3非上市生产 4非上市测试
def get_type_name(type):
    type_name = ''
    if type == '1':
        type_name = '1上市生产'
    elif type == '2':
        type_name = '2上市测试'
    elif type == '3':
        type_name = '3非上市生产'
    elif type == '4':
        type_name = '4非上市测试'
    return type_name


def find_video_duration(type, begin_time, end_time):
    type_name = get_type_name(type)
    duration = 0
    # 获取数据库连接
    con = get_connection(type)

    # 查询已经关闭的观看时长
    sql = "select sum(TIMESTAMPDIFF(SECOND, start_time, end_time)) duration from iot_camera_use_record a where 1=1 " \
          "and a.start_time is not null and a.end_time is not null and a.start_time " \
          "between '{}' and '{}'".format(begin_time, end_time)
    rs = select_by_sql(con, sql)
    rs = json.loads(rs)
    d = rs[0]['duration']
    if d == 'None':
        d = '0'
    duration = duration + int(d)
    print("环境【%s】已关闭的使用时长【%d】秒" % (type_name, int(d)))

    # 查询正在观看的视频时长
    sql2 = "select sum(TIMESTAMPDIFF(SECOND, start_time, now())) duration from iot_camera_use_record a where 1=1 " \
           "and a.start_time is not null and a.end_time is null and a.start_time " \
           "between '{}' and '{}'".format(begin_time, end_time)
    rs = select_by_sql(con, sql2)
    rs = json.loads(rs)
    d = rs[0]['duration']
    if d == 'None':
        d = '0'
    duration = duration + int(d)
    print("环境【%s】正在看的使用时长【%d】秒" % (type_name, int(d)))

    # 计算总时长
    print("环境【%s】使用时长【%d】秒" % (type_name, duration))
    con.close()
    return duration


if __name__ == '__main__':
    duration = 0
    begin_time = '2024-05-13 00:00:00'
    end_time = '2024-5-19 23:59:59'
    for i in (1, 2, 3, 4):
        # 查询时间
        d = find_video_duration(str(i), begin_time, end_time)
        duration = duration + d
    print("时间范围【%s - %s】" % (begin_time, end_time))
    print("使用总时长【%d】秒" % (duration))
    print("使用总时长【%.2f】分钟" % (duration / 60))
    print("使用总时长【%.2f】小时" % (round(duration / 3600, 2)))
